<?php
include_once "dbconnect.php";

$xml = simplexml_load_file('data.xml');

echo "START INSERT CONTINENTS <br />";
foreach($xml->continent as $continent)
{
    //Insert continents to DB

    $query = "INSERT INTO continents (id, name) VALUES ('$continent[id]','$continent[name]')" or die("Error in the consult.." . mysqli_error($this->db));
    mysqli_query($link, $query);
}
echo "INSERTED CONTINENTS<br />";

echo "INSERT COUNTRIES";
foreach($xml->country as $country)
{
    $countryid =  $country["id"];
    $country_name = $country["name"];
    $country_population = $country["population"];
    $country_carcode =  $country["car_code"];
    $country_capital =  $country["capital"];
    $country_continent = $country->encompassed["continent"];

    $query = "INSERT INTO countries (id, name, population, car_code, capital, continentid) VALUES ('$countryid', '$country_name', '$country_population', '$country_carcode', '$country_capital', '$country_continent')" or die("Error in the consult.." . mysqli_error($this->db));
    mysqli_query($link, $query);

    foreach($country->city as $city)
    {
        $city_id = mysqli_real_escape_string($link, $city["id"]);
        $city_name = mysqli_real_escape_string($link,$city->name);
        $city_population = $city->population;
        if(empty($city_population))
        {
            $city_population = 0;
        }
        $city_country = mysqli_real_escape_string($link,$city["country"]);

        $query = "INSERT INTO city (id, name, population, countryid) VALUES ('$city_id', '$city_name', $city_population, '$city_country')" or die("Error in the consult.." . mysqli_error($this->db));
        mysqli_query($link, $query) or die ('Unable to execute query. '. mysqli_error($link) . $city_name);
    }
}
?>